package org.kingtop.mybatis.plugin.parser;

import java.util.List;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.ValuesList;
import net.sf.jsqlparser.statement.select.WithItem;

/**
 * 优化统计条数的SQL语句
 *
 * @author 陈金波
 * @date   2018年1月5日 下午3:46:01    
 * @Copyright: 2018 kingtop.
 */
public class TotalSqlOptimizer implements ISqlOptimizer {
	
	private static final String QUESTION_MARK_REPLACE = "QUESTION_MARK_REPLACE";

	@Override
	public String optimizeSql(String sql) {
		try {
			if(sql != null && !"".equals(sql)) {
				return removeOrderBy(sql);
			}
		} catch (JSQLParserException e) {
			e.printStackTrace();
		}
		return sql;
	}

	/**
	 * 去除order by语句
	 * @author 陈金波  
	 * @date   2018年1月5日 下午3:46:58  
	 * @param sql
	 * @return
	 * @throws JSQLParserException
	 * @return String
	 */
	protected String removeOrderBy(String sql) throws JSQLParserException {
		sql = sql.replaceAll("[?]", QUESTION_MARK_REPLACE);//替换语句中的问号，不然有时候处理会出现问题
		Statement stmt = CCJSqlParserUtil.parse(sql);
		if(stmt instanceof Select){
			Select select = (Select) stmt;
			SelectBody selectBody = select.getSelectBody();
			processSelectBody(selectBody);
			sql = select.toString();
			sql = sql.replaceAll(QUESTION_MARK_REPLACE, "?");
			return sql;
		}
		return sql;
	}

	protected void processSelectBody(SelectBody selectBody) {
		if (selectBody instanceof PlainSelect) {
			processPlainSelect((PlainSelect) selectBody);
		} else if (selectBody instanceof WithItem) {
			WithItem withItem = (WithItem) selectBody;
			if (withItem.getSelectBody() != null) {
				processSelectBody(withItem.getSelectBody());
			}
		} else {
			SetOperationList operationList = (SetOperationList) selectBody;
			if (operationList.getSelects() != null && operationList.getSelects().size() > 0) {
				List<SelectBody> plainSelects = operationList.getSelects();
				for (SelectBody plainSelect : plainSelects) {
					processPlainSelect((PlainSelect) plainSelect);
				}
			}
			if (!orderByHashParameters(operationList.getOrderByElements())) {
				operationList.setOrderByElements(null);
			}
		}
	}

	protected void processPlainSelect(PlainSelect plainSelect) {
		if (!orderByHashParameters(plainSelect.getOrderByElements())) {
			plainSelect.setOrderByElements(null);
		}
		if (plainSelect.getFromItem() != null) {
			processFromItem(plainSelect.getFromItem());
		}
		if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
			List<Join> joins = plainSelect.getJoins();
			for (Join join : joins) {
				if (join.getRightItem() != null) {
					processFromItem(join.getRightItem());
				}
			}
		}
	}

	protected void processFromItem(FromItem fromItem) {
		if (fromItem instanceof SubJoin) {
			SubJoin subJoin = (SubJoin) fromItem;
			if (subJoin.getJoin() != null) {
				if (subJoin.getJoin().getRightItem() != null) {
					processFromItem(subJoin.getJoin().getRightItem());
				}
			}
			if (subJoin.getLeft() != null) {
				processFromItem(subJoin.getLeft());
			}
		} else if (fromItem instanceof SubSelect) {
			SubSelect subSelect = (SubSelect) fromItem;
			if (subSelect.getSelectBody() != null) {
				processSelectBody(subSelect.getSelectBody());
			}
		} else if (fromItem instanceof ValuesList) {

		} else if (fromItem instanceof LateralSubSelect) {
			LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
			if (lateralSubSelect.getSubSelect() != null) {
				SubSelect subSelect = (SubSelect) (lateralSubSelect.getSubSelect());
				if (subSelect.getSelectBody() != null) {
					processSelectBody(subSelect.getSelectBody());
				}
			}
		}
		// Table时不用处理
	}

	/**
	 * 判断是否包含order by，如果包含，判断排序的字段是否有变量
	 * @author 陈金波  
	 * @date   2018年1月5日 下午3:48:18  
	 * @param orderByElements
	 * @return
	 * @return boolean
	 */
	protected boolean orderByHashParameters(List<OrderByElement> orderByElements) {
		if (orderByElements == null) {
			return false;
		}
		for (OrderByElement orderByElement : orderByElements) {
			if (orderByElement.toString().toUpperCase().contains("?")) {
				return true;
			}
		}
		return false;
	}

}
